import json
import requests
import pymysql


# 协同平-安全管理-航运公司安全管理系统-监督检查-日常监督检查-检查实施
BASE_URL = "http://cmp.msa.gov.cn/ShippingSecurity/CompanyInspect_Local.action"

headers = {
    'Accept': '*/*',
    # Content-Type设置为application/json，接口才能返回json数据
    'Content-Type': 'application/x-www-form-urlencoded; charset=UTF-8'
}
cookies = {
    # OAMAuthnCookie_cmp.msa.gov.cn:80 参数用于登录认证
    'OAMAuthnCookie_cmp.msa.gov.cn:80': '0oe1NumGF8HYUA6G7vmNphZt6n2apWNqJGCFqxaVBYfgwr9WnNY4AkHnaMvQnsr6NBZ6fv8BHK0e3nlDhJv3fLcbDaaM38EMz5TOmjSyTaUoeQOkxl2cQYsIee',
    # OAMAuthnHintCookie和JSESSIONID 参数用于声明返回接口数据，不配置的会一直返回HTML
    'OAMAuthnHintCookie': '1',
    'JSESSIONID': 'OBaDwUT5NBkBOfqfaTxeNEjkpJJSsF7JW4bXuKKjcWNVodRuCom3'
}

#组装请求配置
session = requests.Session()
session.headers.update(headers)


param = {
    'JsonValues':'{"table":{"tableName":"Pub_Query","row":{"CurrentPage":"1","PageRecord":"300","DateType":"3","StartDate":"2024-10-01","EndDate":"2024-12-31","table":{"tableName":"Pub_Org","row":[{"OrgCode":"140000"},{"OrgCode":"140100"},{"OrgCode":"140200"},{"OrgCode":"140300"},{"OrgCode":"140400"},{"OrgCode":"140500"},{"OrgCode":"140600"}]}}}}',
    'MethodValue':'getInspectList'
}

response_lo = session.post(BASE_URL,data=param,headers=headers, cookies=cookies)

# 响应参数
strData = json.loads(json.loads(response_lo.text))
print(strData)
#获取到具体数据的位置
strRecordsData = strData['datas']['table']['row']['table']['row']

if response_lo.status_code == 200:
    # 数据库连接配置
    db_config = {
        'host': '191.254.8.220',
        'user': 'root',
        'password': 'Szmsa@520',
        'db': 'spiders',
        'charset': 'utf8mb4',
        'cursorclass': pymysql.cursors.DictCursor
    }
    # 表名和字段名
    table_name = 'xt_safety_company_check'

    # 连接数据库
    connection = pymysql.connect(**db_config)

try:
    # 开始事务
    with connection.cursor() as cursor:
        # 循环插入每条记录
        for record in strRecordsData:
            # 获取需要插入的字段名称
            fields = ', '.join(record.keys())

            # 拼装需要插入字段对应的%号个数
            values = ", ".join(['%s'] * len(record))

            # 构建插入语句，插入业务主表
            insert_stmt = (
                "INSERT INTO `{table}` ({fields}) "
                "VALUES ({values})"
            ).format(
                table=table_name,
                fields=fields,
                values=values
            )
            # 插入语句
            # 执行sql语句插入数据，record.values()用于获取字段中的值
            cursor.execute(insert_stmt, tuple(record.values()))

    # 提交事务
    connection.commit()
finally:
    # 关闭数据库连接
    connection.close()

print('插入完成！')

